﻿using System;
using System.IO;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

using Common.ADO.Tools;
namespace Common.IO
{
    /// <summary>
    /// 文件操作类
    /// </summary>
    public static class File
    {
        static object obj = new object();
        /// <summary>
        /// 读取文本文件
        /// </summary>
        /// <param name="FilePath"></param>
        /// <returns></returns>
        public static string ReadTxtFile(string FilePath)
        {
            StreamReader TxtReader = new StreamReader(@"" + FilePath, System.Text.Encoding.Default);
            string FileContent = TxtReader.ReadToEnd();
            TxtReader.Dispose();
            TxtReader.Close();
            return FileContent;
        }
        /// <summary>
        /// 写入文件
        /// </summary>
        /// <param name="bytes"></param>
        public static void WriteFile(byte[] bytes, string filename)
        {
            string str = System.Text.Encoding.UTF8.GetString(bytes);
            WriteTxt(filename, str);
        }

        /// <summary>
        /// 从网上下载文件
        /// </summary>
        /// <param name="FileURL">文件路径</param>
        /// <param name="SavePath">文件保存路径</param>
        /// <returns></returns>
        public static void DownloadFile(string FileURL, string SavePath)
        {
            System.Net.WebClient wc = new System.Net.WebClient();
            wc.DownloadFile(FileURL, SavePath);
        }


        /// <summary>
        /// 逐行读取文本内容
        /// </summary>
        /// <param name="filepath"></param>
        /// <returns></returns>
        public static List<string> ReadFile(string filepath)
        {
            StreamReader TxtReader = new StreamReader(@"" + filepath, System.Text.Encoding.Default);
            List<string> strs = new List<string>();
            while (!TxtReader.EndOfStream)
            {
                strs.Add(TxtReader.ReadLine());
            }
            TxtReader.Close();
            return strs;
        }

        /// <summary>
        ///  创建一个TXT文件
        /// </summary>
        /// <param name="FILE_NAME">文件路径</param>
        /// <param name="str">内容</param>
        public static void WriteTxt(string FILE_NAME, string str)
        {
            StreamWriter sr = null;
            string folder = FILE_NAME.Substring(0, FILE_NAME.LastIndexOf("/"));
            if (!System.IO.Directory.Exists(folder))
            {
                Directory.CreateDirectory(folder);
            }
            if (!System.IO.File.Exists(FILE_NAME)) //如果文件不存在,则创建File.AppendText对象
            {

                sr = System.IO.File.CreateText(FILE_NAME);
                sr.WriteLine(str);
                lock (obj)
                {
                    sr.Flush();
                    sr.Close();
                }
                sr.Dispose();


            }
            else   //如果文件不存在,则创建File.CreateText对象
            {
                System.IO.File.Delete(FILE_NAME);
                WriteTxt(FILE_NAME, str);
            }
        }

        /// <summary>
        ///  创建一个TXT文件
        /// </summary>
        /// <param name="FILE_NAME">文件路径</param>
        /// <param name="str">内容</param>
        public static void WriteFile(string FILE_NAME, string str)
        {
            System.IO.StreamWriter sw = null;
            sw = System.IO.File.AppendText(FILE_NAME);
            sw.WriteLine(str);
            lock (obj)
            {
                sw.Flush();
                sw.Close();
            }

        }

        /// <summary>
        /// 创建HTML文件
        /// </summary>
        /// <param name="FILE_NAME"></param>
        /// <param name="str"></param>
        public static void WriteHTML(string FILE_NAME, string str)
        {
            if (System.IO.File.Exists(FILE_NAME)) //如果文件存在,则创建File.AppendText对象
            {
                System.IO.File.Delete(FILE_NAME);
            }
            FileStream fs = new FileStream(FILE_NAME, FileMode.OpenOrCreate, FileAccess.ReadWrite);//如果文件不存在的话，新建一个。存在的话，直接写入。
            StreamWriter writer = new StreamWriter(fs, System.Text.Encoding.GetEncoding("utf-8"));
            writer.WriteLine(HttpUtility.HtmlDecode(str));
            writer.Close();

        }

        /// <summary>
        /// 读取Excel
        /// </summary>
        /// <param name="FilePath">文件路径</param>
        /// <param name="SheetName">页码名称</param>
        /// <returns></returns>
        public static DataTable GetTable(string FilePath, string SheetName)
        {
            try
            {
                string conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + FilePath + ";Extended Properties=Excel 8.0;";

                //Sheet1为excel中表的名字
                string sql = "select * from [" + SheetName + "$]";
                OleDbCommand cmd = new OleDbCommand(sql, new OleDbConnection(conn));
                OleDbDataAdapter ad = new OleDbDataAdapter(cmd);
                DataSet ds = new DataSet();
                ad.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 根据SQL语句把查询到的内容保存入EXCEL
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <param name="sheetName">页面名称</param>
        /// <param name="filepath">保存文件全路径</param>
        /// <returns></returns>
        public static bool SQLToExcel(string cmdText, string sheetName, string filepath)
        {
            SqlDataReader sdr = DbHelperSQL.ExecuteReader(cmdText);
            Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            xlApp.Visible = false;
            object objMissing = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Workbook mybook = (Microsoft.Office.Interop.Excel.Workbook)xlApp.Workbooks.Add(1);   //   1表示只建一个表
            //mybook.Worksheets.Add(objMissing,objMissing,1,objMissing);//添加sheet

            ((Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[1]).Name = sheetName;//将sheet1的名称改为zhu
            Microsoft.Office.Interop.Excel.Worksheet mysheet = (Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[1];
            //    ((Microsoft.Office.Interop.Excel.Range)mysheet.Cells[2, 3]).EntireRow.Insert(0);     //添加行
            //    mysheet.Cells[2, 3] = "zhu ";//赋值

            int row = 2;
            int fieldcount = sdr.FieldCount;
            for (int col = 0; col < fieldcount; col++) { xlApp.Cells[1, col + 1] = sdr.GetName(col); }
            while (sdr.Read())
            {
                for (int col = 0; col < fieldcount; col++)
                    xlApp.Cells[row, col + 1] = sdr.GetValue(col).ToString();
                row++;
            }
            //保存
            mybook.SaveAs(filepath, objMissing, objMissing, objMissing,   //Excel.XlSaveAsAccessMode.xlShared
            objMissing, objMissing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
            objMissing, objMissing, objMissing, objMissing,
            objMissing);
            mybook = null;
            xlApp.Quit();
            xlApp = null;

            return true;
        }

    }


}
